package com.vecspace.dayu.code.dao;

import java.util.List;
import org.springframework.stereotype.Repository;

import com.vecspace.database.dao.BaseDao;
import com.vecspace.dayu.code.pojo.DataTable;

/** DAYU-表视图定义,按顺序号进行left join,操作数据库的dao类
 * @author: wanghua
 * @reviewer:
 */
@Repository("dayu_dataTableDao")
public class DataTableDao extends BaseDao {
	/** 根据ID查询 DAYU-表视图定义,按顺序号进行left join
	 * @param id   主键值
	 * @return DataTable
	 * @throws Exception
	 * @author wanghua
	 */
	public DataTable getDataTable(Long id) throws Exception {
		DataTable dataTable = null;
		if (id != null && id != 0) {

			dataTable = this.queryObject(DataTable.class, id);
			this.evict(dataTable);
		}
		return dataTable;
	}
	
	/**获取数据集下的主表（第一个表）
	 * 
	 * @param datasetId
	 * @param tableName
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	public DataTable getMainDataTable(Long datasetId, String tableName) throws Exception {
		DataTable dataTable = this.queryObject("from DataTable where datasetId=?0 and tableName=?1 and (joinColumns is null or joinColumns='')", 
				new Object[] {datasetId, tableName});
		
		return dataTable;
	}
	
	/**获取数据集涉及的数据表
	 * 
	 * @param datasetId
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	public List<DataTable> getDataTables(Long funcId) throws Exception {
		List<DataTable> dataTables = this.queryObjectList("from DataTable where funcId=?0 order by ordnum", new Object[] {funcId});
		return dataTables;
	}

	/**获取项目内的数据表
	 * 
	 * @param datasetId
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	public List<DataTable> getProjectDataTables(Long projectId) throws Exception {
		String sql = "SELECT t1.ID,t1.FUNC_ID,t1.TABLE_NAME,t1.TABLE_CAPTION FROM DAYU_CODE_DATA_TABLE T1 LEFT JOIN  DAYU_CODE_FUNC T2 ON T1.FUNC_ID=T2.ID WHERE T2.PROJECT_ID=?";
		List<DataTable> dataTables = dao.queryObjectListBySql(DataTable.class, sql, new Object[] {projectId});
		return dataTables;
	}
	
	/**获取数据集下指定表名的数据表
	 * 
	 * @param datasetId
	 * @return
	 * @throws Exception 
	 * @author wanghua
	 */
	public List<DataTable> getDataTables(Long datasetId, String tableName) throws Exception {
		List<DataTable> dataTables = this.queryObjectList("from DataTable where datasetId=?0 and tableName=?1 order by ordnum", new Object[] {datasetId, tableName});
		return dataTables;
	}

	/** 插入或修改 DAYU-表视图定义,按顺序号进行left join 
	 * @param item	要保存的对象
	 * @return void
	 * @throws Exception
	 * @author wanghua
	 */
	public void saveDataTable(DataTable item) throws Exception {
		this.saveOrUpdateObject(item);
		this.evict(item);
	}
	
	/**更新单个字段的值 
	 * @param columnName
	 * @param id
	 * @param value
	 * @return 返回更新的记录数
	 * @throws Exception 
	 * @author wanghua
	 */
	public int updateValue(Long id, String columnName, Object value) throws Exception {
		String tableName = "DAYU_CODE_DATA_TABLE";
		String idColumn = "ID";
		return super.updateValue(tableName, idColumn, columnName, id, value);
	}

	/** 根据ID删除 DAYU-表视图定义,按顺序号进行left join 
	 * @param id	主键值
	 * @return void
	 * @throws Exception
	 * @author wanghua
	 */
	public void deleteDataTable(Long id) throws Exception {
		if (id != null && id != 0) {
			this.deleteObject(DataTable.class, id);
			Long tableId = id;
			this.deleteDataRowBySql("delete from DAYU_CODE_UI_DATA_GRID_COLUMN where DATAGRID_ID in (select ID from DAYU_CODE_UI_DATAGRID where TABLE_ID=?)", new Object[] {tableId});
			this.deleteDataRowBySql("delete from DAYU_CODE_UI_FORM_FIELD where FORM_ID in (select ID from DAYU_CODE_UI_FORM where TABLE_ID=?)", new Object[] {tableId});
			
			this.deleteDataRowBySql("delete from DAYU_CODE_UI_DATA_TREE where TABLE_ID=?", new Object[] {tableId});
			this.deleteDataRowBySql("delete from DAYU_CODE_UI_DATAGRID where TABLE_ID=?", new Object[] {tableId});
			this.deleteDataRowBySql("delete from DAYU_CODE_UI_FORM where TABLE_ID=?", new Object[] {tableId});
			
			this.deleteDataRowBySql("delete from DAYU_CODE_DATA_COLUMN where DATATABLE_ID=?", new Object[] {tableId});
		}
	}
}
